package com.example.transter;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zbc
 * @description TODO
 * @ClassName {name}
 * @email 2010_10_zhong@sina.com
 * @date 2020/11/12 17:57
 */
public class Main {

    public static void main(String[] args) {
        try {
            System.out.println("=============");
            String path = "C:\\Users\\EDZ\\Downloads\\1.xlsx";
            // 用输入流从本地拿到对应的Excel文件
            InputStream stream = new FileInputStream(path);
            // 指定要生成的xml的路径，并构建文件对象
            File f = new File("test.xml");// 新建个file对象把解析之后得到的xml存入改文件中
            writerXML(stream, f);// 将数据以xml形式写入文本
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static void writerXML(InputStream stream, File f) throws IOException {
        System.out.println("into writerXML");
        FileOutputStream fo = new FileOutputStream(f);// 得到输入流
        Document doc = readExcell(stream);// 读取EXCEL函数
        Format format = Format.getCompactFormat().setEncoding("UTF-8").setIndent("");
        XMLOutputter XMLOut = new XMLOutputter(format);// 在元素后换行，每一层元素缩排四格
        XMLOut.output(doc, fo);
        fo.close();
    }

    private static Document readExcell(InputStream stream) {
        System.out.println("into readExcell");
        // 设置根<tax_institutions></tax_institutions>元素
        Element root = new Element("tax_institutions");
        Document doc = new Document(root);
        try {
            XSSFWorkbook hw = new XSSFWorkbook(stream);
            // 获取工作薄的个数，即一个excel文件中包含了多少个Sheet工作簿
            int WbLength = hw.getNumberOfSheets();
            System.out.println("WbLength=" + WbLength);

            for (int i = 0; i < WbLength; i++) {
                XSSFSheet shee = hw.getSheetAt(i);
                int length = shee.getLastRowNum();
                System.out.println("行数：" + length);
                for (int j = 1; j <= length; j++) {
                    XSSFRow row = shee.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    List<String> list = new ArrayList<>();
                    int cellNum = row.getPhysicalNumberOfCells();// 获取一行中最后一个单元格的位置
                    for (int k = 0; k < cellNum; k++) {
                        XSSFCell cell = row.getCell(k);
                        String stringCellValue = cell.toString();
                        list.add(stringCellValue);
                    }
                    //把list 值转换成 xml 文件
                    toXmlFile(list);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stream.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }

        }
        return doc;
    }

    private static void toXmlFile(List<String> list) throws ParserConfigurationException {
        DomXml.createXml(list);
    }

}
